package zqservlet;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.thoughtworks.xstream.XStream;
import com.thoughtworks.xstream.io.xml.DomDriver;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import net.sf.json.JSONObject;

import zqdb.O_INFO;
import zqtools.DBUtil;
import zqtools.Test;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;

@WebServlet("/downsave")
public class downsaveServlet extends HttpServlet {
    public static O_INFO of;

    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");

        of = new O_INFO();
        Enumeration<String> parameterNames = request.getParameterNames();
        while (parameterNames.hasMoreElements()) {
            String s = parameterNames.nextElement();
            switch (s) {
                case "URL":
                    of.setURL(request.getParameter(s));
                    break;
                case "CMD":
                    of.setCMD(request.getParameter(s));
                    break;
                case "VALUE01":
                    of.setVALUE01(request.getParameter(s));
                    break;
                case "VALUE02":
                    of.setVALUE02(request.getParameter(s));
                    break;
                case "VALUE03":
                    of.setVALUE03(request.getParameter(s));
                    break;
                case "VALUE04":
                    of.setVALUE04(request.getParameter(s));
                    break;
                case "VALUE05":
                    of.setVALUE05(request.getParameter(s));
                    break;
                case "VALUE06":
                    of.setVALUE06(request.getParameter(s));
                    break;
                case "VALUE07":
                    of.setVALUE07(request.getParameter(s));
                    break;
                case "VALUE08":
                    of.setVALUE08(request.getParameter(s));
                    break;
                case "VALUE09":
                    of.setVALUE09(request.getParameter(s));
                    break;
                case "VALUE10":
                    of.setVALUE10(request.getParameter(s));
                    break;
                case "VALUE11":
                    of.setVALUE11(request.getParameter(s));
                    break;
                case "VALUE12":
                    of.setVALUE12(request.getParameter(s));
                    break;
                case "VALUE13":
                    of.setVALUE13(request.getParameter(s));
                    break;
                case "VALUE14":
                    of.setVALUE14(request.getParameter(s));
                    break;
                case "VALUE15":
                    of.setVALUE15(request.getParameter(s));
                    break;
                case "VALUE16":
                    of.setVALUE16(request.getParameter(s));
                    break;
                case "VALUE17":
                    of.setVALUE17(request.getParameter(s));
                    break;
                case "VALUE18":
                    of.setVALUE18(request.getParameter(s));
                    break;
                case "VALUE19":
                    of.setVALUE19(request.getParameter(s));
                    break;
                case "VALUE20":
                    of.setVALUE20(request.getParameter(s));
                    break;
                case "PIC1":
                    of.setPIC1(request.getParameter(s));
                    break;
                case "PIC2":
                    of.setPIC2(request.getParameter(s));
                    break;
                case "PIC3":
                    of.setPIC3(request.getParameter(s));
                    break;
                case "PIC4":
                    of.setPIC4(request.getParameter(s));
                    break;
                case "PIC5":
                    of.setPIC5(request.getParameter(s));
                    break;
                case "PIC6":
                    of.setPIC6(request.getParameter(s));
                    break;
                case "PIC7":
                    of.setPIC7(request.getParameter(s));
                    break;
                case "PIC8":
                    of.setPIC8(request.getParameter(s));
                    break;
                case "PIC9":
                    of.setPIC9(request.getParameter(s));
                    break;
            }
        }

        simpleWrite();

        JSONObject j = new JSONObject();
        j.put("CODE","ZZ00");
        response.getWriter().print(j);
        j.clear();
    }

    private static List<xflscxData> data() {
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_INFO.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        String ls_sql = "{call WP_PC2(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        List<xflscxData> list = ListUtils.newArrayList();
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            //获取结果集
            ResultSet rs1 = callsm.getResultSet();
            while (rs1.next()) {
                xflscxData data = new xflscxData();
                data.setGh(rs1.getString("JOB_NO"));
                data.setXm(rs1.getString("L_NAME"));
                data.setBm(rs1.getString("L_BY4"));
                data.setJysj(rs1.getString("JYSJ"));
                data.setQcje(Double.valueOf(rs1.getString("L_JE1")));
                data.setXfje(Double.valueOf(rs1.getString("L_JE5")));
                data.setZhjy(Double.valueOf(rs1.getString("L_JE7")));
                data.setXfbl(Double.valueOf(rs1.getString("L_JE8")));
                data.setSkxs(rs1.getString("L_BY2"));
                data.setLx(rs1.getString("B_JUD"));
                data.setZd(rs1.getString("L_JQLS"));
                data.setSh(rs1.getString("L_BY1"));
                data.setLb(rs1.getString("Z_IDD"));
                data.setSj(rs1.getString("SCSJ"));
                data.setBz(rs1.getString("L_BY5"));

                list.add(data);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    public static void simpleWrite() {
        String fileName = "c:\\消费流水表.xls";
        EasyExcel.write(fileName, xflscxData.class).excelType(ExcelTypeEnum.XLSX)
                .sheet("会员列表")
                .doWrite(downsaveServlet::data);

    }
}

@Getter
@Setter
@ContentRowHeight(22)
@ContentFontStyle(fontHeightInPoints = 12)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//表头样式,默认就是居中
@EqualsAndHashCode
class xflscxData {
    @ExcelProperty("工号")
    @ColumnWidth(12)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String gh;

    @ExcelProperty("姓名")
    @ColumnWidth(10)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String xm;

    @ExcelProperty("部门")
    @ColumnWidth(38)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String bm;

    @ExcelProperty("交易时间")
    @ColumnWidth(22)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String jysj;

    @ExcelProperty("上传时间")
    @ColumnWidth(22)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String sj;

    @ExcelProperty("期初金额")
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private Double qcje;

    @ExcelProperty("消费金额")
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private Double xfje;

    @ExcelProperty("账户结余")
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private Double zhjy;

    @ExcelProperty("消费比率")
    @ColumnWidth(12)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private Double xfbl;

    @ExcelProperty("刷卡形式")
    @ColumnWidth(13)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String skxs;

    @ExcelProperty("类型")
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String lx;

    @ExcelProperty("终端")
    @ColumnWidth(20)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String zd;

    @ExcelProperty("商户")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String sh;

    @ExcelProperty("场景")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String lb;

    @ExcelProperty("备注")
    @ColumnWidth(22)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
    private String bz;


    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}